﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:APP_AUDIT
	/// </summary>
	public partial class APP_AUDIT : BaseDAL<GSLMS.Entity.TB_APP_AUDIT>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public APP_AUDIT()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_APP_AUDIT model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PROID", model.PROID),
								new OracleParameter("P_AUDITTYPE", model.AUDITTYPE),
								new OracleParameter("P_AUDITBY", model.AUDITBY),
								new OracleParameter("P_AUDITTIME", model.AUDITTIME),
								new OracleParameter("P_AUDITRESULT", model.AUDITRESULT),
								new OracleParameter("P_AUDITCONTENT", model.AUDITCONTENT),
                                new OracleParameter("returnvalue",OracleType.Int32)
               };
  
			for (int i = 0; i < 7; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

            parms[7].Direction = ParameterDirection.Output;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_audit_add", parms);
                if(parms[7].Value!=DBNull.Value&&OracleHelper.ToInt(parms[7].Value.ToString())==1)
				  flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_APP_AUDIT model)
		{
			bool flag = false;
            //OracleParameter[] parms =new OracleParameter[]{
            //                    new OracleParameter("P_ID", model.ID),
            //                    new OracleParameter("P_PROID", model.PROID),
            //                    new OracleParameter("P_AUDITTYPE", model.AUDITTYPE),
            //                    new OracleParameter("P_AUDITBY", model.AUDITBY),
            //                    new OracleParameter("P_AUDITTIME", model.AUDITTIME),
            //                    new OracleParameter("P_AUDITRESULT", model.AUDITRESULT),
            //                    new OracleParameter("P_AUDITCONTENT", model.AUDITCONTENT)};
  
            //for (int i = 0; i < 7; i++)
            //{
            //    parms[i].Direction = ParameterDirection.Input;
            //}

            //try
            //{
            //    OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_app_audit_update", parms);
            //    flag = true;
            //}
            //catch (Exception ex)
            //{
            //    log.ExceptionHand(ex);
            //}
			return flag;
		}

        /// <summary>
        /// 首席风控官审批(包括同意，拒绝，退回，撤回)
        /// </summary>
        /// <returns>返回1成功，-1不允许审批，0失败</returns>
        public int DirectorAudit(GSLMS.Entity.TB_APP_DIRECTORAUDIT model)
        {
            int num = 0;

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("P_ID",model.ID),
              new OracleParameter("P_PROID",model.PROID),
              new OracleParameter("P_AUDITBY",model.AUDITBY),
              new OracleParameter("P_AUDITTIME",model.AUDITTIME),
              new OracleParameter("P_AUDITRESULT",model.AUDITRESULT),
              new OracleParameter("P_AUDITCONTENT",model.AUDITCONTENT),
              new OracleParameter("P_TOUSER",model.TOUSER),
              new OracleParameter("returnvalue",OracleType.Int32)
            };

            for (int i = 0; i <= 6; i++)
                parms[i].Direction = ParameterDirection.Input;
            parms[7].Direction = ParameterDirection.Output;

            try
            {

                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_directoraudit_add", parms);

                if (parms[7].Value != DBNull.Value)
                    num = OracleHelper.ToInt(parms[7].Value.ToString());

            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

            return num;
        }



        /// <summary>
        /// 判断是否允许首席风控官撤回审批
        /// </summary>
        /// <param name="proID">立项申请记录ID</param>
        /// <returns></returns>
        public bool IsDirectorAuditRetract(long proID,long auditID)
        {
            bool flag = false;

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_proid",proID),
              new OracleParameter("p_auditID",auditID),
              new OracleParameter("returnvalue",OracleType.Int32)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Output;

            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_directoraudit_retract", parms);
                if (parms[2].Value != DBNull.Value && OracleHelper.ToInt(parms[2].Value.ToString()) == 1)
                    flag = true;
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

            return flag;
        }



		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
				OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_app_audit_delete", parms);
				flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_APP_AUDIT DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_APP_AUDIT model = new GSLMS.Entity.TB_APP_AUDIT();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["PROID"]!=null && dr["PROID"].ToString()!="")
				{
					model.PROID=OracleHelper.ToLong(dr["PROID"].ToString());
				}
				if(dr["AUDITTYPE"]!=null && dr["AUDITTYPE"].ToString()!="")
				{
                    model.AUDITTYPE = OracleHelper.ToInt(dr["AUDITTYPE"].ToString());
				}
				if(dr["AUDITBY"]!=null && dr["AUDITBY"].ToString()!="")
				{
					model.AUDITBY=dr["AUDITBY"].ToString();
				}
				if(dr["AUDITTIME"]!=null && dr["AUDITTIME"].ToString()!="")
				{
					model.AUDITTIME=DateTime.Parse(dr["AUDITTIME"].ToString());
				}
				if(dr["AUDITRESULT"]!=null && dr["AUDITRESULT"].ToString()!="")
				{
                    model.AUDITRESULT = OracleHelper.ToInt(dr["AUDITRESULT"].ToString());
				}
				if(dr["AUDITCONTENT"]!=null && dr["AUDITCONTENT"].ToString()!="")
				{
					model.AUDITCONTENT=dr["AUDITCONTENT"].ToString();
				}

                if (dr["CUSTNAME"] != null && dr["CUSTNAME"].ToString() != "")
                {
                    model.CUSTNAME = dr["CUSTNAME"].ToString();
                }

                if (dr["CUSTNO"] != null && dr["CUSTNO"].ToString() != "")
                {
                    model.CUSTNO = dr["CUSTNO"].ToString();
                }

                if (dr["APPLYNO"] != null && dr["APPLYNO"].ToString() != "")
                {
                    model.APPLYNO = dr["APPLYNO"].ToString();
                }


				return model;
			}
			else
			{
				return null;
			}
		}

        /// <summary>
        ///DataRowToDirectorModel 
        /// </summary>
        private GSLMS.Entity.TB_APP_DIRECTORAUDIT DataRowToDirectorModel(DataRow dr)
        {
            if (dr != null && dr.ItemArray.Length > 0)
            {
                GSLMS.Entity.TB_APP_DIRECTORAUDIT model = new GSLMS.Entity.TB_APP_DIRECTORAUDIT();
                if (dr["ID"] != null && dr["ID"].ToString() != "")
                {
                    model.ID = OracleHelper.ToLong(dr["ID"].ToString());
                }
                if (dr["PROID"] != null && dr["PROID"].ToString() != "")
                {
                    model.PROID = OracleHelper.ToLong(dr["PROID"].ToString());
                }
               
                if (dr["AUDITBY"] != null && dr["AUDITBY"].ToString() != "")
                {
                    model.AUDITBY = dr["AUDITBY"].ToString();
                }
                if (dr["AUDITTIME"] != null && dr["AUDITTIME"].ToString() != "")
                {
                    model.AUDITTIME = DateTime.Parse(dr["AUDITTIME"].ToString());
                }
                if (dr["AUDITRESULT"] != null && dr["AUDITRESULT"].ToString() != "")
                {
                    model.AUDITRESULT = OracleHelper.ToInt(dr["AUDITRESULT"].ToString());
                }
                if (dr["AUDITCONTENT"] != null && dr["AUDITCONTENT"].ToString() != "")
                {
                    model.AUDITCONTENT = dr["AUDITCONTENT"].ToString();
                }

                if (dr["CUSTNAME"] != null && dr["CUSTNAME"].ToString() != "")
                {
                    model.CUSTNAME = dr["CUSTNAME"].ToString();
                }

                if (dr["CUSTNO"] != null && dr["CUSTNO"].ToString() != "")
                {
                    model.CUSTNO = dr["CUSTNO"].ToString();
                }

                if (dr["APPLYNO"] != null && dr["APPLYNO"].ToString() != "")
                {
                    model.APPLYNO = dr["APPLYNO"].ToString();
                }

                if (dr["touser"] != null && dr["touser"].ToString() != "")
                {
                    model.TOUSER = dr["touser"].ToString();
                }

                if (dr["tousername"] != null && dr["tousername"].ToString() != "")
                {
                    model.TOUSERNAME = dr["tousername"].ToString();
                }


                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///DataRowToModel 
        /// </summary>
        private GSLMS.Entity.TB_ADU_CONSCHEME DataRowToShemeModel(DataRow dr)
        {
            if (dr != null && dr.ItemArray.Length > 0)
            {
                GSLMS.Entity.TB_ADU_CONSCHEME model = new GSLMS.Entity.TB_ADU_CONSCHEME();
                if (dr["ID"] != null && dr["ID"].ToString() != "")
                {
                    model.ID = OracleHelper.ToLong(dr["ID"].ToString());
                }
                if (dr["PROID"] != null && dr["PROID"].ToString() != "")
                {
                    model.PROID = OracleHelper.ToLong(dr["PROID"].ToString());
                }
                if (dr["ARRID"] != null && dr["ARRID"].ToString() != "")
                {
                    model.ARRID = OracleHelper.ToLong(dr["ARRID"].ToString());
                }
                if (dr["PROGNO"] != null && dr["PROGNO"].ToString() != "")
                {
                    model.PROGNO = OracleHelper.ToInt(dr["PROGNO"].ToString());
                }
                if (dr["PROGCAT"] != null && dr["PROGCAT"].ToString() != "")
                {
                    model.PROGCAT = dr["PROGCAT"].ToString();
                }
                if (dr["AMT"] != null && dr["AMT"].ToString() != "")
                {
                    model.AMT = OracleHelper.ToDecimal(dr["AMT"].ToString());
                }
                if (dr["RATE"] != null && dr["RATE"].ToString() != "")
                {
                    model.RATE = OracleHelper.ToDecimal(dr["RATE"].ToString());
                }
                if (dr["MANAGERATE"] != null && dr["MANAGERATE"].ToString() != "")
                {
                    model.MANAGERATE = OracleHelper.ToDecimal(dr["MANAGERATE"].ToString());
                }
                if (dr["GENERALRATE"] != null && dr["GENERALRATE"].ToString() != "")
                {
                    model.GENERALRATE = OracleHelper.ToDecimal(dr["GENERALRATE"].ToString());
                }
                if (dr["LOANTIME"] != null && dr["LOANTIME"].ToString() != "")
                {
                    model.LOANTIME = OracleHelper.ToInt(dr["LOANTIME"].ToString());
                }
                if (dr["LOANPHASE"] != null && dr["LOANPHASE"].ToString() != "")
                {
                    model.LOANPHASE = OracleHelper.ToInt(dr["LOANPHASE"].ToString());
                }
                if (dr["LOANPHASETYPE"] != null && dr["LOANPHASETYPE"].ToString() != "")
                {
                    model.LOANPHASETYPE = OracleHelper.ToInt(dr["LOANPHASETYPE"].ToString());
                }
                if (dr["ADVAMT"] != null && dr["ADVAMT"].ToString() != "")
                {
                    model.ADVAMT = OracleHelper.ToDecimal(dr["ADVAMT"].ToString());
                }
                if (dr["AUDITREMARK"] != null && dr["AUDITREMARK"].ToString() != "")
                {
                    model.AUDITREMARK = dr["AUDITREMARK"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_APP_AUDIT GetByID(long ID)
		{
			GSLMS.Entity.TB_APP_AUDIT sModel = new GSLMS.Entity.TB_APP_AUDIT();
			string sql = "";
            sql = "select * from V_APP_AUDIT where id=" + ID.ToString();
			DataSet ds = OracleHelper.ExecuteDataset(sql);
			if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				sModel = DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
			return null;
			}
			return sModel;
		}

		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_APP_AUDIT> GetList(GSLMS.Entity.TB_APP_AUDIT searchParam)
		{
			var list=new List<GSLMS.Entity.TB_APP_AUDIT>();
			DataTable dt = null;
            string tblName = "V_APP_AUDIT";
			string sqlWhere="";
			string strSort="";
			int pageSize=20;
			int pageIndex=1;
			string strFields="*";
			int totalCount = 0;
			int countFlag = 1;

            //是否计算总数
            countFlag = (searchParam.ReCount != null && searchParam.ReCount) ? 1 : 0;
            pageSize = (searchParam.PageSize != null && searchParam.PageSize > 0) ? searchParam.PageSize : 15;
            pageIndex = (searchParam.CurrentIndex != null && searchParam.CurrentIndex > 0) ? searchParam.CurrentIndex : 1;

            if (searchParam.CUSTNAME != null && searchParam.CUSTNAME.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNAME) + "%'";
            }

            if (searchParam.CUSTNO != null && searchParam.CUSTNO.Trim() != "")
            {
                sqlWhere += " and CUSTNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNO) + "%'";
            }

            if (searchParam.APPLYNO != null && searchParam.APPLYNO.Trim() != "")
            {
                sqlWhere += " and APPLYNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.APPLYNO) + "%'";
            }

            if (searchParam.AUDITBY != null && searchParam.AUDITBY.Trim() != "")
            {
                sqlWhere += " and AUDITBY='" + OracleHelper.ReplaceUnsafeSql(searchParam.AUDITBY) + "'";
            }

            if (searchParam.AUDITTYPE > 0)
            {
                sqlWhere += " and AUDITTYPE=" + searchParam.AUDITTYPE.ToString();
            }

            if (searchParam.AUDITTIME != null && searchParam.AUDITTIME.ToString().Trim() != "")
            {
                sqlWhere += " and AUDITTIME>=to_date('" + Convert.ToDateTime(searchParam.AUDITTIME).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.AUDITTIME_END != null && searchParam.AUDITTIME_END.ToString().Trim() != "")
            {
                sqlWhere += " and AUDITTIME<to_date('" + Convert.ToDateTime(searchParam.AUDITTIME_END).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);

            if (searchParam.ReCount)
                searchParam.RowCount = totalCount;

			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_APP_AUDIT model = new GSLMS.Entity.TB_APP_AUDIT();
					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			else
			{
				return null;
			}
			return list;
		}


        /// <summary>
        /// 提取首席风控官审批信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <returns></returns>
        public GSLMS.Entity.TB_APP_DIRECTORAUDIT GetDirectorAuditByID(long id)
        {
            var model = new GSLMS.Entity.TB_APP_DIRECTORAUDIT();
            string sql = "";

            sql = "select * from V_APP_DirectorAUDIT where id="+id.ToString();

            DataSet ds = OracleHelper.ExecuteDataset(sql);
            if (ds != null && ds.Tables.Count > 0&&ds.Tables[0].Rows.Count>0)
                model = DataRowToDirectorModel(ds.Tables[0].Rows[0]);
            return model;
        }

        /// <summary>
        /// 分页提取首席风控官审批数据
        /// </summary>
        public List<GSLMS.Entity.TB_APP_DIRECTORAUDIT> GetDirectorAuditList(GSLMS.Entity.TB_APP_DIRECTORAUDIT searchParam)
        {
            var list = new List<GSLMS.Entity.TB_APP_DIRECTORAUDIT>();
            DataTable dt = null;
            string tblName = "V_APP_DirectorAUDIT";
            string sqlWhere = "";
            string strSort = "";
            int pageSize = 20;
            int pageIndex = 1;
            string strFields = "*";
            int totalCount = 0;
            int countFlag = 1;

            //是否计算总数
            countFlag = (searchParam.Pagination.ReCount != null && searchParam.Pagination.ReCount) ? 1 : 0;
            pageSize = (searchParam.Pagination.PageSize != null && searchParam.Pagination.PageSize > 0) ? searchParam.Pagination.PageSize : 15;
            pageIndex = (searchParam.Pagination.CurrentIndex != null && searchParam.Pagination.CurrentIndex > 0) ? searchParam.Pagination.CurrentIndex : 1;

            if (searchParam.CUSTNAME != null && searchParam.CUSTNAME.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNAME.Trim()) + "%'";
            }

            if (searchParam.CUSTNO != null && searchParam.CUSTNO.Trim() != "")
            {
                sqlWhere += " and CUSTNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNO.Trim()) + "%'";
            }

            if (searchParam.APPLYNO != null && searchParam.APPLYNO.Trim() != "")
            {
                sqlWhere += " and APPLYNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.APPLYNO.Trim()) + "%'";
            }

            if (searchParam.AUDITBY != null && searchParam.AUDITBY.Trim() != "")
            {
                sqlWhere += " and AUDITBY='" + OracleHelper.ReplaceUnsafeSql(searchParam.AUDITBY.Trim()) + "'";
            }


            if (searchParam.AUDITTIME != null && searchParam.AUDITTIME.ToString().Trim() != "")
            {
                sqlWhere += " and AUDITTIME>=to_date('" + Convert.ToDateTime(searchParam.AUDITTIME).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.AUDITTIME_END != null && searchParam.AUDITTIME_END.ToString().Trim() != "")
            {
                sqlWhere += " and AUDITTIME<to_date('" + Convert.ToDateTime(searchParam.AUDITTIME_END).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            //系统记录ID
            if (searchParam.SystemID > 0)
            {
                sqlWhere += " and systemid=" + searchParam.SystemID.ToString();
            }

            dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex, countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_APP_DIRECTORAUDIT model = new GSLMS.Entity.TB_APP_DIRECTORAUDIT();
                    model =DataRowToDirectorModel(dt.Rows[i]);

                    if (dt.Rows[i]["systemid"] != null && dt.Rows[i]["systemid"].ToString().Trim() != "")
                        model.SystemID = OracleHelper.ToInt(dt.Rows[i]["systemid"].ToString());

                    list.Add(model);
                }
            }
            else
            {
                return null;
            }
            return list;
        }


        /// <summary>
        /// 提取首席风控官审批中要显示的项目经理和总经理审批的信息
        /// </summary>
        /// <param name="proID"></param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_APP_AUDIT> GetDirectorAuditInfo(long proID)
        {
            var list = new List<GSLMS.Entity.TB_APP_AUDIT>();
            DataTable dt = null;

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_proid",proID),
              new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_directoraudit_auditinfo", parms);

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            { 
              for(int i=0;i<dt.Rows.Count;i++)
                  list.Add(DataRowToModel(dt.Rows[i]));
            }
            

            return list;
        }

        /// <summary>
        /// 提取审议审批中要显示的项目经理和总经理审批的信息
        /// </summary>
        /// <param name="proID"></param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_APP_AUDIT> GetRiskAuditConAduAuditInfo(long proID)
        {
            var list = new List<GSLMS.Entity.TB_APP_AUDIT>();
            DataTable dt = null;

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_proid",proID),
              new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_riskauditconadu_auditinfo", parms);

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                    list.Add(DataRowToModel(dt.Rows[i]));
            }


            return list;
        }

        /// <summary>
        /// 提取总裁、董事长要显示的审批信息
        /// </summary>
        /// <param name="proID"></param>
        /// <returns></returns>
        public GSLMS.Entity.HighAuditInfo GetHighAuditInfo(long proID)
        {
            var highAuditInfo = new GSLMS.Entity.HighAuditInfo();
            var list = new List<GSLMS.Entity.TB_APP_AUDIT>();
            DataTable dt = null;
            DataTable cDt = null;//审议汇总的方案信息
            DataTable gDt = null;//总裁、董事长的方案信息

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_proid",proID),
              new OracleParameter("p_cur",OracleType.Cursor),
              new OracleParameter("p_cscur",OracleType.Cursor),
              new OracleParameter("p_gscur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;
            parms[2].Direction = ParameterDirection.Output;
            parms[3].Direction = ParameterDirection.Output;


            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_highaudit_auditinfo", parms);

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
                if (ds.Tables.Count > 1)
                    cDt = ds.Tables[1];
                if (ds.Tables.Count > 2)
                    gDt = ds.Tables[2];
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                    list.Add(DataRowToModel(dt.Rows[i]));

                highAuditInfo.appAuditList = list;
            }

            if (cDt != null && cDt.Rows.Count > 0)
            {
                var conSchemeList = new List<GSLMS.Entity.TB_ADU_CONSCHEME>();
                for (int i = 0; i < cDt.Rows.Count; i++)
                    conSchemeList.Add(DataRowToShemeModel(cDt.Rows[i]));

                highAuditInfo.conAduitSchemeList = conSchemeList;
            }

            if (gDt != null && gDt.Rows.Count > 0)
            {
                var highSchemeList = new List<GSLMS.Entity.TB_ADU_CONSCHEME>();
                for (int i = 0; i < gDt.Rows.Count; i++)
                    highSchemeList.Add(DataRowToShemeModel(gDt.Rows[i]));

                highAuditInfo.highAduitSchemeList = highSchemeList;
            }


            return highAuditInfo
;
        }

        /// <summary>
        ///DataRowToModel 
        /// </summary>
        private GSLMS.Entity.TB_APP_SHOULDDOINFO DataRowToShouldDoModel(DataRow dr)
        {
            if (dr != null && dr.ItemArray.Length > 0)
            {
                GSLMS.Entity.TB_APP_SHOULDDOINFO model = new GSLMS.Entity.TB_APP_SHOULDDOINFO();

        //         提出日期
        //操作
        //客户名称
        //证件号码
        //产品名称
        //产品记录ID
        //类别,1待分配，2立项申请待审批，3转介申请待处理，4特批申请待审批，5展期申请待审批，6费用减免待审批

        //private DateTime? _applyDate;
        //private string _operType;
        //private string _custName;
        //private string _custNo;
        //private string _productName;
        //private long _productID;
        //private int _InfoType;
        //private long _pid;


                if (dr["PID"] != null && dr["PID"].ToString() != "")
                {
                    model.PID = OracleHelper.ToLong(dr["PID"].ToString());
                }
                if (dr["ApplyDate"] != null && dr["ApplyDate"].ToString() != "")
                {
                    model.ApplyDate =Convert.ToDateTime(dr["ApplyDate"].ToString());
                }
                if (dr["OperType"] != null && dr["OperType"].ToString() != "")
                {
                    model.OperType = dr["OperType"].ToString();
                }
                if (dr["CustName"] != null && dr["CustName"].ToString() != "")
                {
                    model.CustName = dr["CustName"].ToString();
                }
                if (dr["CustNO"] != null && dr["CustNO"].ToString() != "")
                {
                    model.CustNO = dr["CustNO"].ToString();
                }
                if (dr["PRODUCTID"] != null && dr["PRODUCTID"].ToString() != "")
                {
                    model.PorductID = OracleHelper.ToLong(dr["PRODUCTID"].ToString());
                }
                if (dr["ProductName"] != null && dr["ProductName"].ToString() != "")
                {
                    model.ProductName = dr["ProductName"].ToString();
                }

                if (dr["InfoType"] != null && dr["InfoType"].ToString() != "")
                {
                    model.InfoType = OracleHelper.ToInt(dr["InfoType"].ToString());
                }
               
                return model;
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// 提取部门经理待审批信息
        /// </summary>
        /// <param name="actionBy">提取人</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_APP_SHOULDDOINFO> GetBMShouldDoList(string actionBy)
        {
            var list = new List<GSLMS.Entity.TB_APP_SHOULDDOINFO>();

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_actionby",actionBy),
              new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_bm_aduit_get", parms);
            DataTable dt = null;
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var model = new GSLMS.Entity.TB_APP_SHOULDDOINFO();
                    model = DataRowToShouldDoModel(dt.Rows[i]);
                    model.AuditType = 1;
                    list.Add(model);
                }
                 
            }

            return list;
        }

        /// <summary>
        /// 提取项目总经理待审批信息
        /// </summary>
        /// <param name="actionBy">提取人</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_APP_SHOULDDOINFO> GetGMShouldDoList(string actionBy)
        {
            var list = new List<GSLMS.Entity.TB_APP_SHOULDDOINFO>();

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_actionby",actionBy),
              new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_gm_aduit_get", parms);
            DataTable dt = null;
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var model = new GSLMS.Entity.TB_APP_SHOULDDOINFO();
                    model = DataRowToShouldDoModel(dt.Rows[i]);
                    model.AuditType = 2;
                    list.Add(model);
                }

            }

            return list;
        }




        /// <summary>
        /// 立项申请项目经理审批
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ProInfoBMAudit(GSLMS.Entity.TB_APP_AUDIT model)
        {
            return Add(model);
        }

        /// <summary>
        /// 转介申请审批
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool RefInfoAudit(GSLMS.Entity.TB_APP_REFREGINFO model)
        {
            bool flag = false;

            OracleParameter[] parms = new OracleParameter[] { 
               new OracleParameter("p_id",model.ID),
               new OracleParameter("p_indepid",model.INDEPID),
               new OracleParameter("p_indepname",model.INDEPNAME),
               new OracleParameter("p_status",model.STATUS),
               new OracleParameter("p_auditby",model.AUDITBY),
               new OracleParameter("p_backstatus",model.BACKSTATUS),
               new OracleParameter("returnvalue",OracleType.Int32)
            };

            for (int i = 0; i < 6; i++)
                parms[i].Direction = ParameterDirection.Input;

            parms[6].Direction = ParameterDirection.Output;

            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_refreginfo_audit", parms);
                if (parms[6].Value != DBNull.Value && OracleHelper.ToInt(parms[6].Value.ToString()) == 1)
                    flag = true;
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }
               
                return flag;
        }

        /// <summary>
        /// 新增转介分配信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool RefInfoAss(GSLMS.Entity.TB_APP_REFREGINFO model)
        {
            bool flag = false;

            OracleParameter[] parms = new OracleParameter[] { 
               new OracleParameter("p_id",model.ID),
               new OracleParameter("P_REFREGID",model.ID),
               new OracleParameter("P_CUSTNAME",model.CUSTNAME),
               new OracleParameter("P_CUSTNO",model.SPAREFIELD2),
               new OracleParameter("P_SALESMAN",model.SALESMAN),
               new OracleParameter("P_SALESMANID",model.SALESMANID),
               new OracleParameter("P_ASSDATE",DateTime.Now),
               new OracleParameter("P_ASSBY",model.AUDITBY)
            };




            for (int i = 0; i < 8; i++)
                parms[i].Direction = ParameterDirection.Input;

            

            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_refregass_add", parms);
                flag = true;
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

       
            return flag;

        }


        /// <summary>
        /// 提取首席风控官待审批信息
        /// </summary>
        /// <param name="actionBy">提取人</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_APP_SHOULDDOINFO> GetRiskAuditShouldDoList(string actionBy)
        {
            var list = new List<GSLMS.Entity.TB_APP_SHOULDDOINFO>();

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_actionby",actionBy),
              new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_directoraduit_get", parms);
            DataTable dt = null;
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var model = new GSLMS.Entity.TB_APP_SHOULDDOINFO();
                    model = DataRowToShouldDoModel(dt.Rows[i]);
                    if (dt.Rows[i]["systemid"] != null)
                        model.SystemID = Convert.ToInt32(dt.Rows[i]["systemid"].ToString());
                    model.AuditType = 2;
                    list.Add(model);
                }

            }

            return list;
        }

		#endregion  Method

        #region 总裁、董事长审批

        /// <summary>
        /// 提取总裁、董事长待审批信息
        /// </summary>
        /// <param name="actionBy">提取人</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_APP_SHOULDDOINFO> GetHighAuditShouldDoList(string actionBy)
        {
            var list = new List<GSLMS.Entity.TB_APP_SHOULDDOINFO>();

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_actionby",actionBy),
              new OracleParameter("p_cur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_Highaduit_get", parms);
            DataTable dt = null;
            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var model = new GSLMS.Entity.TB_APP_SHOULDDOINFO();
                    model = DataRowToShouldDoModel(dt.Rows[i]);
                    if (dt.Rows[i]["systemid"] != null)
                        model.SystemID = Convert.ToInt32(dt.Rows[i]["systemid"].ToString());
                    model.AuditType = 2;
                    list.Add(model);
                }

            }

            return list;
        }

        /// <summary>
        /// 总裁、董事长审批(包括同意，拒绝，退回，撤回)
        /// </summary>
        /// <returns>返回1成功，-1不允许审批，0失败</returns>
        public int ToHighAudit(GSLMS.Entity.TB_APP_DIRECTORAUDIT model)
        {
            int num = 0;

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("P_ID",model.ID),
              new OracleParameter("P_PROID",model.PROID),
              new OracleParameter("P_AUDITBY",model.AUDITBY),
              new OracleParameter("P_AUDITTIME",model.AUDITTIME),
              new OracleParameter("P_AUDITRESULT",model.AUDITRESULT),
              new OracleParameter("P_AUDITCONTENT",model.AUDITCONTENT),
              new OracleParameter("returnvalue",OracleType.Int32)
            };

            for (int i = 0; i <= 5; i++)
                parms[i].Direction = ParameterDirection.Input;
            parms[6].Direction = ParameterDirection.Output;

            try
            {

                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_ToHighAudit_Update", parms);

                if (parms[6].Value != DBNull.Value)
                {
                    num = OracleHelper.ToInt(parms[6].Value.ToString());

                    if (num == 1&&model.AUDITRESULT==1 && model.AduConSchemeList != null && model.AduConSchemeList.Count > 0)
                    {
                        if (!AddHighAuditCheMeList(model.AduConSchemeList, model.PROID))
                        {
                            num = 0;
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

            return num;
        }


        /// <summary>
        ///  增加总裁、董事长的方案信息
        /// </summary>
        public bool AddHighAuditCheMeList(List<GSLMS.Entity.TB_ADU_CONSCHEME> list, long proID)
        {
            bool flag = false;
            int n = 1;
            if (list != null && list.Count > 0)
            {
                foreach (var model in list)
                {
                    OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PROID", proID),
								new OracleParameter("P_ARRID", model.ARRID),
								new OracleParameter("P_PROGNO", n++),
								new OracleParameter("P_PROGCAT", model.PROGCAT),
								new OracleParameter("P_AMT", model.AMT),
								new OracleParameter("P_RATE", model.RATE),
								new OracleParameter("P_MANAGERATE", model.MANAGERATE),
								new OracleParameter("P_GENERALRATE", model.GENERALRATE),
								new OracleParameter("P_LOANTIME", model.LOANTIME),
								new OracleParameter("P_LOANPHASE", model.LOANPHASE),
								new OracleParameter("P_LOANPHASETYPE", model.LOANPHASETYPE),
								new OracleParameter("P_ADVAMT", model.ADVAMT),
								new OracleParameter("P_AUDITREMARK", model.AUDITREMARK)};

                    for (int i = 0; i < 14; i++)
                    {
                        parms[i].Direction = ParameterDirection.Input;
                    }

                    try
                    {
                        OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_highauditscheme_add", parms);
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        log.ExceptionHand(ex);
                        flag = false;
                        break;

                    }
                }
            }
            else
                flag = true;
            return flag;
        }

        /// <summary>
        /// 分页提取总裁、董事长审批数据
        /// </summary>
        public List<GSLMS.Entity.TB_APP_DIRECTORAUDIT> GetHighAuditList(GSLMS.Entity.TB_APP_DIRECTORAUDIT searchParam)
        {
            var list = new List<GSLMS.Entity.TB_APP_DIRECTORAUDIT>();
            DataTable dt = null;
            string tblName = "V_APP_HIGHAUDIT";
            string sqlWhere = "";
            string strSort = "";
            int pageSize = 20;
            int pageIndex = 1;
            string strFields = "*";
            int totalCount = 0;
            int countFlag = 1;

            //是否计算总数
            countFlag = (searchParam.Pagination.ReCount != null && searchParam.Pagination.ReCount) ? 1 : 0;
            pageSize = (searchParam.Pagination.PageSize != null && searchParam.Pagination.PageSize > 0) ? searchParam.Pagination.PageSize : 15;
            pageIndex = (searchParam.Pagination.CurrentIndex != null && searchParam.Pagination.CurrentIndex > 0) ? searchParam.Pagination.CurrentIndex : 1;

            if (searchParam.CUSTNAME != null && searchParam.CUSTNAME.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNAME.Trim()) + "%'";
            }

            if (searchParam.CUSTNO != null && searchParam.CUSTNO.Trim() != "")
            {
                sqlWhere += " and CUSTNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNO.Trim()) + "%'";
            }

            if (searchParam.APPLYNO != null && searchParam.APPLYNO.Trim() != "")
            {
                sqlWhere += " and APPLYNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.APPLYNO.Trim()) + "%'";
            }

            if (searchParam.AUDITBY != null && searchParam.AUDITBY.Trim() != "")
            {
                sqlWhere += " and AUDITBY='" + OracleHelper.ReplaceUnsafeSql(searchParam.AUDITBY.Trim()) + "'";
            }


            if (searchParam.AUDITTIME != null && searchParam.AUDITTIME.ToString().Trim() != "")
            {
                sqlWhere += " and AUDITTIME>=to_date('" + Convert.ToDateTime(searchParam.AUDITTIME).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.AUDITTIME_END != null && searchParam.AUDITTIME_END.ToString().Trim() != "")
            {
                sqlWhere += " and AUDITTIME<to_date('" + Convert.ToDateTime(searchParam.AUDITTIME_END).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            //系统记录ID
            if (searchParam.SystemID > 0)
            {
                sqlWhere += " and systemid=" + searchParam.SystemID.ToString();
            }

            dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex, countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_APP_DIRECTORAUDIT model = new GSLMS.Entity.TB_APP_DIRECTORAUDIT();
                    model = DataRowToDirectorModel(dt.Rows[i]);

                    if (dt.Rows[i]["systemid"] != null && dt.Rows[i]["systemid"].ToString().Trim() != "")
                        model.SystemID = OracleHelper.ToInt(dt.Rows[i]["systemid"].ToString());

                    list.Add(model);
                }
            }
            else
            {
                return null;
            }
            return list;
        }

        /// <summary>
        /// 判断是否允许总裁、董事长撤回审批
        /// </summary>
        /// <param name="proID">立项申请记录ID</param>
        /// <returns></returns>
        public bool IsHighAuditRetract(long proID, long auditID)
        {
            bool flag = false;

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_proid",proID),
              new OracleParameter("p_auditID",auditID),
              new OracleParameter("returnvalue",OracleType.Int32)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Output;

            try
            {
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_audit.proc_highaudit_retract", parms);
                if (parms[2].Value != DBNull.Value && OracleHelper.ToInt(parms[2].Value.ToString()) == 1)
                    flag = true;
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

            return flag;
        }


        #endregion
	}
}

